﻿'
'OLE DB Database Helper
'written by Bram Debouvere 
'getreader & executescalar with transaction by Andrei Clinciu
'
Option Strict On

Imports System.Data.OleDb
Imports System.Configuration



Public Class DatabaseHelper

    Private ConnectionString As String
    Private Connection As OleDbConnection

    Public Property DBConnection As OleDbConnection
        Get
            Return Me.Connection
        End Get
        Set(ByVal Value As OleDbConnection)
            Me.Connection = Value
        End Set
    End Property

    Public Property DBConnectionString As String
        Get
            Return Me.ConnectionString
        End Get
        Set(ByVal Value As String)
            Me.ConnectionString = Value
        End Set
    End Property


    Public Sub New(Optional ByVal ConnectionStringName As String = "ConnectionString")
        Me.ConnectionString = GetConnectionString(ConnectionStringName)
        Me.Connection = GetConnection(ConnectionStringName)
    End Sub

    Public Function GetConnection(Optional ByVal Name As String = "ConnectionString") As OleDbConnection
        Dim conn As New OleDbConnection()
        conn.ConnectionString = GetConnectionString(Name)
        conn.Open()
        Return conn
    End Function

    Public Function GetConnectionString(ByVal Name As String) As String
        Return ConfigurationManager.ConnectionStrings(Name).ConnectionString()
    End Function

    Public Function GetCommand(ByVal SQL As String, ByVal ParameterArray() As Object, Optional ByVal CustomConnection As OleDbConnection = Nothing) As OleDbCommand
        'get connection
        If (CustomConnection Is Nothing) Then
            CustomConnection = Me.Connection
        End If
        'create command object
        Dim oCom As New OleDbCommand(SQL, CustomConnection)
        'add parameters
        AddParameters(oCom, ParameterArray)

        Return oCom
    End Function

    Public Function GetCommand(ByVal SQL As String, ByVal ParameterArray() As Object, ByRef Transaction As OleDbTransaction, Optional ByVal CustomConnection As OleDbConnection = Nothing) As OleDbCommand
        'get connection
        If (CustomConnection Is Nothing) Then
            CustomConnection = Me.Connection
        End If
        'create command object
        Dim oCom As New OleDbCommand(SQL, CustomConnection, Transaction)
        'add parameters
        AddParameters(oCom, ParameterArray)

        Return oCom
    End Function

    'parameter adding for the GetCommand function
    Private Sub AddParameters(ByRef OCom As OleDbCommand, ByRef ParameterArray() As Object)
        If Not ParameterArray.Length = 0 Then
            For Each param As Object In ParameterArray
                Dim oleParam As New OleDbParameter()
                oleParam.Value = param
                OCom.Parameters.Add(oleParam)
            Next
        End If
    End Sub

    Public Function GetReader(ByVal SQL As String, ByVal ParameterArray() As Object, Optional ByVal CustomConnection As OleDbConnection = Nothing) As OleDbDataReader
        'get command
        Dim oleCommand As OleDbCommand = GetCommand(SQL, ParameterArray, CustomConnection)
        Dim reader As OleDbDataReader = oleCommand.ExecuteReader()
        Return reader
    End Function

    Public Function GetReader(ByVal SQL As String, ByVal ParameterArray() As Object, ByRef Transaction As OleDbTransaction, Optional ByVal CustomConnection As OleDbConnection = Nothing) As OleDbDataReader
        'get command
        Dim oleCommand As OleDbCommand = GetCommand(SQL, ParameterArray, Transaction, CustomConnection)
        Dim reader As OleDbDataReader = oleCommand.ExecuteReader()
        Return reader
    End Function

    'normal SQL executer - use KeepConnectionLive if you do not want the connection to close after the query
    Public Sub ExecuteSQL(ByVal SQL As String, ByVal ParameterArray() As Object, Optional ByVal KeepConnectionLive As Boolean = False, Optional ByVal CostumConnection As OleDbConnection = Nothing)
        'get command
        Dim oleCommand As OleDbCommand = GetCommand(SQL, ParameterArray, CostumConnection)
        oleCommand.ExecuteNonQuery()
        If Not KeepConnectionLive Then
            Connection.Close()
        End If
    End Sub

    'SQL executer for use with transactions - the connection will never close automaticly here
    Public Sub ExecuteSQL(ByVal SQL As String, ByVal ParameterArray() As Object, ByRef Transaction As OleDbTransaction, Optional ByVal CostumConnection As OleDbConnection = Nothing)
        'get command
        Dim oleCommand As OleDbCommand = GetCommand(SQL, ParameterArray, Transaction, CostumConnection)
        oleCommand.ExecuteNonQuery()
    End Sub

    'execute scalar used within transactions mostly to get id's
    Public Function ExecuteScalar(ByVal SQL As String, ByVal ParameterArray() As Object, ByRef Transaction As OleDbTransaction, Optional ByVal CustomConnection As OleDbConnection = Nothing) As Object
        'get command
        Dim oleCommand As OleDbCommand = GetCommand(SQL, ParameterArray, Transaction, CustomConnection)
        Return oleCommand.ExecuteScalar()
    End Function

End Class
